﻿using System;
using System.Data;
using System.Configuration;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using ContractManagermentSystem.Entity;

namespace ContractManagermentSystem.Business
{
    public class ContractBus
    {
        public static void ImportCSV(string path)
        {
            //CSV File
            //ContractTypeId
            //ConsultantId
            //ClientId
            //Title
            //CreateDate
            //StartDate
            //EndDate
            //Value
            //Description
            //ContractorFullName
            //ContracorPhoneNumber1
            //ContracorPhoneNumber2
            //Gender
            //Branch
            //SortCode
            var dataTable = ParseCSV(path);
            var full = Path.GetFullPath(path);
            var file = Path.GetFileName(full);
            foreach (DataRow dataRow in dataTable.Rows)
            {
                var contract = new Contract();
                contract.ContractTypeId = dataRow["F1"].ToString();
                contract.ConsultantId = dataRow["F2"].ToString();
                contract.ClientId = dataRow["F3"].ToString();
                contract.Title = dataRow["F4"].ToString();
                contract.Group = file;
                contract.CreateDate = DateTime.Parse(dataRow["F5"].ToString());
                contract.StartDate = DateTime.Parse(dataRow["F6"].ToString());
                contract.EndDate = DateTime.Parse(dataRow["F7"].ToString());
                contract.Value = Double.Parse(dataRow["F8"].ToString());
                contract.Description = dataRow["F9"].ToString();
                var contractor = new Contractor();
                contractor.FullName = dataRow["F10"].ToString();
                contractor.PhoneNumber1 = dataRow["F11"].ToString();
                contractor.PhoneNumber2 = string.IsNullOrEmpty(dataRow["F12"].ToString()) ? "Null" : dataRow["F12"].ToString();
                contractor.Gender = dataRow["F13"].ToString();
                contractor.Branch = dataRow["F14"].ToString();
                contractor.SortCode = dataRow["F15"].ToString();
            }
        }

        public static DataTable ParseCSV(string path)
        {
            if (!File.Exists(path))
                return null;

            var full = Path.GetFullPath(path);
            var file = Path.GetFileName(full);
            var dir = Path.GetDirectoryName(full);

            var connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
              + "Data Source=\"" + dir + "\\\";"
              + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

            var query = "SELECT * FROM " + file;

            var dTable = new DataTable();

            var dAdapter = new OleDbDataAdapter(query, connString) {AcceptChangesDuringFill = true};
            try
            {
                //fill the DataTable
                dAdapter.Fill(dTable);
            }

            catch (InvalidOperationException /*e*/)
            { }
            dAdapter.Update(dTable.Select());
            dAdapter.Dispose();
            return dTable;
        }

        
    }
}
